package com.apes.hr.base.repository;

import com.apes.framework.jpa.repository.CustomRepository;
import com.apes.hr.base.model.*;
import com.apes.scm.masterdata.frame.model.Branch;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import java.util.Date;
import java.util.List;

/**
 * @author linwz
 * @create 2019-02-26 16:07
 */
public interface TransferRecordRepository extends CustomRepository<TransferRecord, Long>{

    @Query("select T from TransferRecord T where 1 = 1 and valid = 1  and transferOn <=:transferOut and (transferOut>=:transferOn or transferOut is null ) ")
    List<TransferRecord> findByTransferOnAndTransferOut(@Param("transferOn")  Date transferOn ,@Param("transferOut") Date transferOut);

    @Query(value = "SELECT T.* FROM HR_TRANSFER_RECORD T WHERE t.valid=1 and t.transfer_on <=?2 and (t.transfer_out>=?1 or t.transfer_out is null ) and t.post_id in(?3) ",nativeQuery = true)
    List<TransferRecord> findByPostAndTransferOnAndTransferOut(Date transferOn ,Date transferOut,List<String> postList);

//    @Query(value = "SELECT t.id as id"+
//            "        FROM HR_TRANSFER_RECORD T," +
//            "        HR_TRANSFER_APPLICATION_ITEM ITEM" +
//            "        WHERE ITEM.ID = T.TRANSFER_APPLICATION_ITEM_ID" +
//            "         AND T.VALID = 1" +
//            "         AND T.TRANSFER_ON <= to_date(?2, 'yyyy-mm-dd')" +
//            "         AND (T.TRANSFER_OUT >= to_date(?1, 'yyyy-mm-dd') OR T.TRANSFER_OUT IS NULL)" +
//            "         AND T.POST_ID IN (?3)",nativeQuery = true)
//  List<TransferRecordVo> findByPostAndTransferOnAndTransferOutNew(String transferOn , String transferOut, List<String> postList);
//

//    @Query("SELECT new map(t.employee as employee,t.workBranch as branch,t.post as post,t.onDutyState as onDutyState," +
//            "t.transferOn as transferOn,t.transferOut as transferOut)" +
//            " FROM TransferRecord T" +
//            "        WHERE T.valid = 1" +
//            "         AND T.transferOn <= ?2" +
//            "         AND (T.transferOut >= ?1 OR T.transferOut IS NULL)" +
//            "         AND T.post IN (?3)")
//    List<Map<String,Object>> findByPostAndTransferOnAndTransferOutNewJpa(Date transferOn , Date transferOut, List<String> postList);


    @Query(value = "SELECT T.*," +
            "       (SELECT SUM(a.value)" +
            "         FROM HR_REWARD_PUNISH c, HR_REWARD_PUNISH_ITEM A" +
            "        WHERE c.ID = A.REWARD_PUNISH_ID" +
            "          AND c.REWARD_DATE >=  to_char(ADD_MONTHS(?1,-12),'yyyy-mm-dd')" +
            "          AND c.STATE = 'sendOut'" +
            "          AND A.EMPLOYEE_ID = t.employee_id) valueTotal," +
            "        GET_ENUMERATION_NAME(T.ON_DUTY_STATE, 'onDutyState')" +
            "        ON_DUTY_STATE," +
            "       " +
            "        (SELECT DESCRIPTION" +
            "                 FROM MAS_ENUMERATION" +
            "                WHERE ID = ITEM.TRANSFER_KIND) TRANSFER_KIND" +
            "       " +
            "         FROM HR_TRANSFER_RECORD T," +
            "        HR_TRANSFER_APPLICATION_ITEM ITEM" +
            "        WHERE ITEM.ID = T.TRANSFER_APPLICATION_ITEM_ID" +
            "         AND T.VALID = 1" +
            "         AND T.TRANSFER_ON <= ?2" +
            "         AND (T.TRANSFER_OUT >= ?1 OR T.TRANSFER_OUT IS NULL)" +
            "         ",nativeQuery = true)
    List<TransferRecord> findByTransferOnAndTransferOutNew(Date transferOn , Date transferOut);

    @Query(value = "SELECT T.* FROM HR_TRANSFER_RECORD T WHERE t.valid=1  AND t.employee_id in(?1)  ORDER by t.EMPLOYEE_ID,t.TRANSFER_ON ",nativeQuery = true)
    List<TransferRecord> findByEmployee(Employee employee);

    @Query(value = "SELECT T.* FROM HR_TRANSFER_RECORD T WHERE t.valid=1  AND t.employee_id in(?1)  and t.post_id in(?2) ORDER by t.EMPLOYEE_ID,t.TRANSFER_ON ",nativeQuery = true)
    List<TransferRecord> findByEmployeeAndPostList(Employee employee,List<String> postList);


    @Query(value = "SELECT T.* FROM HR_TRANSFER_RECORD T WHERE t.valid=1  " +
            "AND t.work_branch_id in?1 and ((t.transfer_on >=?2 and t.transfer_on<=?3)or" +
            " (t.transfer_out >=?2 and t.transfer_on<=?3) or (t.transfer_on>=?2 and t.transfer_out >=?3))" +
            " ORDER by t.EMPLOYEE_ID,t.TRANSFER_ON ",nativeQuery = true)
    List<TransferRecord> findByWorkBranchAndTransferOnAndTransferOut(Branch branch, Date transferOn ,Date transferOut);

    List<TransferRecord> findByWorkBranch(Branch branch);

    @Modifying
    @Query("UPDATE TransferRecord SET transferOut = null WHERE id = ?1")
    int updateTransferOutToNull(Long id);

    TransferRecord findByOldTransferApplicationItemId(String oldId);


    TransferRecord findByTransferApplicationItemId(String id);

    @Query(value = "SELECT T.* FROM HR_TRANSFER_RECORD T WHERE t.valid=1 and t.transfer_on <=?2 and (t.transfer_out>=?1 or t.transfer_out is null ) and t.employee_id = ?3  ",nativeQuery = true)
    List<TransferRecord> findByTransferOnAndTransferOutAndEmployee(Date transferOn ,Date transferOut,String employeeId);


}
